package com.example.video.common.util;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Date;

public class ExcelUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    public static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public static XSSFWorkbook getWorkbook(String fileName) throws IOException {
        File file = new File(fileName);
        if (!file.exists()) {
            throw new IOException("xlsx文件不存在，路径："+fileName);
        }
        FileInputStream fin = new FileInputStream(file);
        //获取工作簿
        return new XSSFWorkbook(fin);
    }

    public static XSSFWorkbook getWorkbook(InputStream fin) throws IOException {

        return new XSSFWorkbook(fin);
    }


    public static String getCellValueAsString(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        }
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue() + "";
        }
        return cell.getStringCellValue();
    }

    public static double getCellValueAsNumeric(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        if (cell.getCellType() == CellType.STRING) {
            //若字符串无法转为数值，则默认为0
            try {
                return Double.parseDouble(cell.getStringCellValue());
            } catch (NumberFormatException e) {
                return 0;
            }
        }
        return cell.getNumericCellValue();
    }

    public static String getCellValueAsDateString(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        }

        if (cell.getCellType() == CellType.NUMERIC) {
            // 使用DateUtil检查该数值是否为日期
            Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
            if (date != null) {
                // 格式化日期为字符串
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return sdf.format(date);
            }
        }
        return "转化错误";
    }

    public static LocalDateTime getCellValueAsLocalDateTime(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        if (cell.getCellType() == CellType.STRING) {
            if (StringUtils.isNotBlank(cell.getStringCellValue())) {
                try {
                    return LocalDateTime.parse(cell.getStringCellValue(), formatter);
                } catch (DateTimeParseException e) {
                    return null;
                }
            }
        }
        if (cell.getCellType() == CellType.NUMERIC) {
            // 使用DateUtil检查该数值是否为日期
            Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
            if (date != null) {
                // 格式化日期为字符串
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String format = sdf.format(date);
                return LocalDateTime.parse(format, formatter);
            }
        }
        return null;
    }

    public static void saveExcel(XSSFWorkbook workbook, String fileName) {
        try {
            // 生成新副本
            String newFile = fileName.replace(".xlsx", "_result.xlsx");
            FileOutputStream fileOutputStream = new FileOutputStream(newFile);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
        } catch (IOException e) {
            LOGGER.info("excel表保存失败！");
        }
    }


    public static void markRowAsRed(XSSFWorkbook workbook, int entityRow) {
        XSSFSheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(entityRow + 1);

        if (row == null) return;

        CellStyle redCellStyle = ExcelUtils.getRedCellStyle(workbook);

        for (int i = 0; i < 40; i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                cell.setCellStyle(redCellStyle);
            }
        }
    }

    public static CellStyle getRedCellStyle(XSSFWorkbook workbook) {
        // 创建红色填充样式
        FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;
        CellStyle rowStyle = workbook.createCellStyle();
        rowStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        rowStyle.setFillPattern(fillPattern);
        return rowStyle;
    }

    public static CellStyle getYellowCellStyle(XSSFWorkbook workbook) {
        // 创建红色填充样式
        FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        cellStyle.setFillPattern(fillPattern);
        return cellStyle;
    }

    public static CellStyle getTopBorderCellStyle(XSSFWorkbook workbook) {
        // 创建一个样式并设置边框
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setBorderTop(BorderStyle.THIN);

        return borderStyle;
    }
}
